package com.show.dao.sys_org;

import com.show.dto.sys_org.SysOrg;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by 顾志杰 on 2018/2/2.
 */
@Repository
public interface SysOrgRepositoty extends JpaRepository<SysOrg, Integer>, JpaSpecificationExecutor<SysOrg> {

    List<SysOrg> findByOrgNameAndSts(String orgName, Integer sts);
    @Query(value = "SELECT * FROM sys_org ee WHERE ee.sts = 1 and ee.parent_org_id = ?" ,nativeQuery = true)
    List<SysOrg> getOrg(Integer pOrgId);

    @Query(value = "SELECT COUNT(*) FROM grid WHERE org_id = ?",nativeQuery = true)
    int getEmp(Integer orgId);


    @Query(value = "SELECT COUNT(*) FROM sys_emp e \n" +
            "LEFT JOIN sys_emp_org o ON e.emp_id=o.emp_id\n" +
            "WHERE e.sts = 1 AND o.org_id = ?",nativeQuery = true)
    int getGrids(Integer orgId);

    @Query(value = "SELECT\n" +
            "	COUNT(*)\n" +
            "FROM\n" +
            "	population p\n" +
            "LEFT JOIN grid gg ON p.grid_id = gg.grid_id\n" +
            "LEFT JOIN sys_org o ON gg.org_id = o.org_id\n" +
            "WHERE\n" +
            "o.org_id =?",nativeQuery = true)
    int getPopulations(Integer orgId);

    @Query(value = "SELECT\n" +
            "	COUNT(*)\n" +
            "FROM\n" +
            "	mz_db_people p\n" +
            "LEFT JOIN grid gg ON p.grid_id = gg.grid_id\n" +
            "LEFT JOIN sys_org o ON gg.org_id = o.org_id\n" +
            "WHERE\n" +
            "o.org_id =?",nativeQuery = true)
    int getDb(Integer orgId);

    @Query(value = "SELECT\n" +
            "	COUNT(*)\n" +
            "FROM\n" +
            "	mz_disabled_people p\n" +
            "LEFT JOIN grid gg ON p.grid_id = gg.grid_id\n" +
            "LEFT JOIN sys_org o ON gg.org_id = o.org_id\n" +
            "WHERE\n" +
            "o.org_id =?",nativeQuery = true)
    int getDisabled(Integer orgId);

    @Query(value = "SELECT\n" +
            "	COUNT(*)\n" +
            "FROM\n" +
            "	mz_sanwu_people p\n" +
            "LEFT JOIN grid gg ON p.grid_id = gg.grid_id\n" +
            "LEFT JOIN sys_org o ON gg.org_id = o.org_id\n" +
            "WHERE\n" +
            "o.org_id =?",nativeQuery = true)
    int getSanWu(Integer orgId);

    @Query(value = "SELECT\n" +
            "	COUNT(*)\n" +
            "FROM\n" +
            "	mz_specialcare_people p\n" +
            "LEFT JOIN grid gg ON p.grid_id = gg.grid_id\n" +
            "LEFT JOIN sys_org o ON gg.org_id = o.org_id\n" +
            "WHERE\n" +
            "o.org_id =?",nativeQuery = true)
    int getSpecial(Integer orgId);

    @Query(value = "SELECT\n" +
            "	COUNT(*)\n" +
            "FROM\n" +
            "	mz_wb_people p\n" +
            "LEFT JOIN grid gg ON p.grid_id = gg.grid_id\n" +
            "LEFT JOIN sys_org o ON gg.org_id = o.org_id\n" +
            "WHERE\n" +
            "o.org_id =?",nativeQuery = true)
    int getWB(Integer orgId);

    @Query(value = "SELECT COUNT(*) FROM mz_leftover_children WHERE org_id =?",nativeQuery = true)
    int getLeftOverChildren(Integer orgId);

    @Query(value = "SELECT COUNT(*) FROM mz_lowincome_people WHERE org_id =?",nativeQuery = true)
    int getLowComeIn(Integer orgId);


}
